|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Tuning Considerations
The batch processing system is tuned for a minimal number of jobs to run at maximum throughput. The system has little concern for response times and much concern for throughput. All the tuning tips described in Part II of this book, Tuning the Server, apply herewith an emphasis on throughput.
Both Oracle and the server operating system may have to be tuned. The following sections look first at Oracle and then at the server operating system.
Oracle Tuning
Analyze these things carefully to determine whether adjustment to these parameters is necessary:
- D B_BLOCK_BUFFERS. Block buffers are not nearly as critical in a batch system as they are in the OLTP system. Still, having an insufficient number of block buffers results in higher-than-normal I/O rates and possibly an I/O bottleneck. The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The ratio of PHYSICAL READS to DB BLOCK GETS and CONSISTENT GETS is the cache-miss ratio, which should be minimized.
- Library cache.Check the V$LIBRARYCACHE table that contains statistics about how well you are using the library cache. The important columns to view in this table are PINS and RELOADS. A low number of reloads relative to the number of executions indicates a high cache-hit rate. You should be able to reduce the number of library cache misses by increasing the amount of memory available for the library cache. Do this by increasing the Oracle tunable parameter SHARED_POOL_SIZE. Increase the size of the shared pool by 10 percent and run again. If that is not sufficient, increase the shared pool by another 10 percent until you are satisfied with the cache-hit rate.
- Multiblock reads. Because many of the queries involve table scans, it is important to make sure that you can take advantage of multiblock reads. The number of blocks read in a multiblock read is specified by the Oracle initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Multiply this value by the value in the DB_BLOCK_SIZE parameter to obtain the maximum size of the I/Os. A good value for these I/Os is 64K.
- Cursor space for time. If you have plenty of memory, you may be able to speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME to TRUE.
- Data dictionary cache. To check the efficiency of the data dictionary cache, check the dynamic performance table V$ROWCACHE. The important columns to view in this table are GETS and GETMISSES. The ratio of GETMISSES to GETS should be low.
- Rollback segments. Depending on the number of updates, you can reduce contention by increasing the number of rollback segments. Unlike an OLTP system, a batch processing system is better off with more rollback segments of a larger size. The size of the rollback segments depends on the size of the transactions. Rollback contention occurs when too many transactions try to use the same rollback segment at the same time and some of them have to wait. You can tell if there is contention on rollback segments by looking at the dynamic performance table V$WAITSTAT. Check for an excessive number of UNDO HEADERs, UNDO BLOCKs, SYSTEM UNDO HEADERs, and SYSTEM UNDO BLOCKs. Compare these to the total number of requests for data. If the number is high, you need more rollback segments.
- Latch contention. Latch contention can be determined by examining the dynamic performance table V$LATCH. Look for the ratio of MISSES to GETS, the number of SLEEPS, and the ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS. If the miss ratio is high, reduce the size of LOG_SMALL_ENTRY_MAX_SIZE to minimize the time any user process holds the latch; alternatively, increase the value of LOG_SIMULTANEOUS_COPIES to reduce contention by adding more redo copy latches. If neither of these parameters helps, set the initialization parameter LOG_ENTRY_PREBUILD_THRESHOLD. Any redo entry of a size smaller than the size you set this parameter must be prebuilt. This reduces the time the latch is held.
- Checkpoints. Under certain circumstances, you may have to tune checkpoints. Although this is usually not necessary, if you see severely degraded performance during checkpoints, you can reduce this effect by enabling the CKPT process. Do so by setting the Oracle initialization parameter CHECKPOINT_PROCESS to TRUE. With a batch processing system, you can usually build the redo log files large enough to defer the checkpoint until after transaction processing is finished.
- Archiving. As with the checkpoint process, you can defer archiving until after transaction processing is complete.
You should pay particular attention to these areas when tuning a system for batch processing. With batch processing, you have to tune not only for optimal transaction processing but for quick load and unload as well. Optimization of the index creation process can also help overall batch performance.
Server OS Tuning
You may have to tune the server OS to provide for a large number of processes and network connections; the server OS also affects the optimization of I/O performance. Following are some of the things you may have to tune in the server OS; keep in mind that some OSes may not require any tuning in these areas:
- Memory. The system should be tuned to reduce unnecessary memory usage so that Oracle can use as much of the systems memory as possible for the SGA and server processes. You may need significant amounts of memory for sorts and joins.
- Memory enhancements. You should take advantage of both 4M pages and ISM if they are available. Both features improve Oracle performance in an batch environment.
- I/O. If necessary, tune I/O to allow for optimal performance and use of AIO.
- Network performance. Because loading and unloading of data is so critical to the overall performance of the batch jobs, you can increase network throughput by adding a high-speed network interface.
- Scheduler. If possible, turn off preemptive scheduling and load balancing. In a batch system, allowing a process to run to completion (that is, not preempting it) is beneficial.
- Cache affinity. With a batch processing system, you may see some benefits from cache affinity. Because the processes tend to run somewhat longer, you may see some benefits.
The server operating system is mainly a host on which Oracle does its job. Any work done by the operating system is essentially overhead for Oracle. By optimizing code paths and reducing OS overhead, you can enhance Oracle performance.
|